﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace LGSVR.Common
{
    public class SQLHelper
    {
        static readonly string ConnStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

        public static DataTable GetDataTable(string sqlText, CommandType cmdType, params SqlParameter[] sqlParam)
        {
            DataTable dt = new DataTable();
            SqlDataReader dr = null;
            SqlConnection myConn = new SqlConnection(ConnStr);
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = sqlText;
                cmd.CommandType = cmdType;
                cmd.Connection = myConn;
                cmd.Parameters.AddRange(sqlParam);
                cmd.Connection.Open();
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                dt.Load(dr);
            }
            catch(Exception ex)
            {
                dt = null;
                throw ex;
            }
            finally
            {
                if (myConn != null)
                {
                    if (myConn.State != ConnectionState.Closed)
                    {
                        myConn.Close();
                    }

                    myConn.Dispose();
                }
            }

            return dt;
        }

        public static object GetScale(string sqlText, CommandType cmdType, params SqlParameter[] sqlParam)
        {
            Object objResult;
            SqlConnection myConn = new SqlConnection(ConnStr);

            objResult = null;

            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = sqlText;
                cmd.CommandType = cmdType;
                cmd.Connection = myConn;
                if (sqlParam != null)
                {
                    cmd.Parameters.AddRange(sqlParam);
                }
                cmd.Connection.Open();
                objResult = cmd.ExecuteScalar();
            }
            catch(Exception ex)
            {
                objResult = null;
                throw ex;
            }
            finally
            {
                if (myConn != null)
                {
                    if (myConn.State != ConnectionState.Closed)
                    {
                        myConn.Close();
                    }

                    myConn.Dispose();
                }
            }

            return objResult;
        }

        public static DataSet GetDataSet(string sqlText, CommandType cmdType, params SqlParameter[] sqlParam)
        {
            DataSet ds;
            SqlDataAdapter sqlAdapt;
            SqlConnection myConn = new SqlConnection(ConnStr);

            ds = new DataSet();
            
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = sqlText;
                cmd.CommandType = cmdType;
                cmd.Connection = myConn;
                cmd.Parameters.AddRange(sqlParam);
                cmd.Connection.Open();
                sqlAdapt = new SqlDataAdapter(cmd);
                sqlAdapt.Fill(ds);
            }
            catch(Exception ex)
            {
                ds = null;
                throw ex;
            }
            finally
            {
                if (myConn != null)
                {
                    if (myConn.State != ConnectionState.Closed)
                    {
                        myConn.Close();
                    }

                    myConn.Dispose();
                }
            }

            return ds;
        }

        public static int ExecuteNonQuery(string sqlText, CommandType cmdType, params SqlParameter[] sqlParam)
        {
            SqlConnection myConn = new SqlConnection(ConnStr);
            int effectRow;

            effectRow = 0;

            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = sqlText;
                cmd.CommandType = cmdType;
                cmd.Connection = myConn;
                cmd.Parameters.AddRange(sqlParam);
                cmd.Connection.Open();
                effectRow = cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            { 
                effectRow = -1;
                throw ex;
            }
            finally
            {
                if (myConn != null)
                {
                    if (myConn.State != ConnectionState.Closed)
                    {
                        myConn.Close();
                    }

                    myConn.Dispose();
                }
            }

            return effectRow;
        }
    }
}
